# -*- coding: UTF-8 -*-
"""
@author: tgy
@file: demo02.py
@time: 2022/02/15
"""
import requests
import random
from hashlib import md5
from openpyxl import load_workbook
from openpyxl import Workbook
import os
import time

# Set your own appid/appkey.
appid = '20220215001083944'
appkey = 'WieBrKza4aroGfsG_qMk'
endpoint = 'http://api.fanyi.baidu.com'
path = '/api/trans/vip/translate'
url = endpoint + path


def translate(query, from_lang="en", to_lang="zh"):
    # For list of language codes, please refer to `https://api.fanyi.baidu.com/doc/21`
    if not query:
        print(f'{query}不能为空')
        return

    # Generate salt and sign
    def make_md5(s, encoding='utf-8'):
        return md5(s.encode(encoding)).hexdigest()

    salt = random.randint(32768, 65536)
    sign = make_md5(appid + query + str(salt) + appkey)

    # Build request
    headers = {'Content-Type': 'application/x-www-form-urlencoded'}
    payload = {'appid': appid, 'q': query, 'from': from_lang, 'to': to_lang, 'salt': salt, 'sign': sign}

    # Send request
    r = requests.post(url, params=payload, headers=headers)
    result = r.json()

    query_dst_res = ""

    for val in result['trans_result']:
        ...
        query_dst_res += (val['dst'] + "\n")

    return query_dst_res


def read_xlsx(path):
    ...
    wb = load_workbook(path)
    sheets = wb.sheetnames
    print(sheets)
    sheet_name = sheets[5]

    # 翻译第一个sheet

    sheet_content = wb[sheet_name]
    rows = sheet_content.rows
    translate_05(sheet_name, rows)


def translate_05(sheet_name,rows):
    ...
    print(sheet_name)
    sava_xlsx = os.path.join("./", sheet_name + ".xlsx")
    save_wb = Workbook()
    save_wb_active = save_wb.active

    flag = True
    index = 0
    for row in rows:
        ...
        line = [col.value for col in row]
        # [53, 'ALUMINUM BROMIDE, ANHYDROUS', '7727-15-3', 1, 'Acids, Strong Non-oxidizing']
        result = []
        if flag:
            ...
            result.extend(line)
            flag = False
            print(result)
        else:
            ...
            result.extend(line[0:5])
            result.append(translate(line[4]))
            # result.append("哈哈")
            result.extend(line[5:])
        save_wb_active.append(result)
        print(result)
    save_wb.save(sava_xlsx)


def translate_02(sheet_name,rows):
    ...
    print(sheet_name)
    sava_xlsx = os.path.join("./", sheet_name + ".xlsx")
    save_wb = Workbook()
    save_wb_active = save_wb.active

    flag = True

    for row in rows:
        ...
        line = [col.value for col in row]
        print(line)
        # # [53, 'ALUMINUM BROMIDE, ANHYDROUS', '7727-15-3', 1, 'Acids, Strong Non-oxidizing']
        # result = []
        # if flag:
        #     ...
        #     result.extend(line)
        #     flag = False
        #     print(result)
        # else:
        #     ...
        #     result.extend(line[0:2])
        #     result.append(translate(line[1]))
        #     result.extend(line[2:])
        #     result.append(translate(line[4]))
        #     # print(result)
        # save_wb_active.append(result)
        # print(result)

    # save_wb.save(sava_xlsx)

def translate_04(sheet_name,rows):
    """

    [1, 'Acids, Strong Non-oxidizing', 0, None, 'Compounds in this group are generally nonflammable.', ' acids in water or the dilution of their concentrated solutions with  mixing to cause some of the water to boil explosively. The resulting "bumping" spatters the acid. These materials react with active metals, including such structural metals as aluminum and iron, to release hydrogen, a flammable gas. They can initiate the polymerization of certain classes of organic compounds. They react with cyanide compounds to release gaseous hydrogen cyanide. They often generate flammable and/or toxic gases in contact with dithiocarbamates, isocyanates, mercaptans, nitrides, nitriles, sulfides, and strong reducing agents. Additional gas-generating reactions occur with sulfites, nitrites, thiosulfates (to give H2S and SO3), dithionites (SO2), and even carbonates: the carbon dioxide gas from the last is non-toxic but the heat and spattering from a rapid reaction can be troublesome. Acids often catalyze (increase the rate of) chemical reactions.', 'Corrosive to tissue. Acid fumes irritate sensitive tissues (such as the eyes and respiratory system) especially severely.', 'Acids have a sour taste; they turn blue litmus red. Strong acids completely dissociate in water to H+ ions and extremely weak conjugate base anions. Non-oxidizing acids give anions that do not act as oxidants.  They possess a pKa value less than -2, or a pH value less than 2.', 'Hydrochloric acid, hydroiodic acid, hydrobromic acid.']
    """
    print(sheet_name)
    sava_xlsx = os.path.join("./", sheet_name + ".xlsx")
    save_wb = Workbook()
    save_wb_active = save_wb.active

    flag = True

    for row in rows:
        ...
        line = [col.value for col in row]
        # [53, 'ALUMINUM BROMIDE, ANHYDROUS', '7727-15-3', 1, 'Acids, Strong Non-oxidizing']
        result = []
        if flag:
            ...
            result.extend(line)
            flag = False
        else:
            ...
            tran_str = ""
            try:
                tran_str = line[1] + "#\n" + line[4] + "#\n" + line[5] + "#\n" + line[6] + "#\n" + line[7] + "#\n" + line[8]
            except Exception as e:
                ...
                continue

            tran_results = translate(tran_str).split("#")
            result.append(line[0])

            result.append(line[1])
            result.append(tran_results[0])

            result.append(line[2])
            result.append(line[3])

            result.append(line[4])
            result.append(tran_results[1].strip("\n"))

            result.append(line[5])
            result.append(tran_results[2].strip("\n"))

            result.append(line[6])
            result.append(tran_results[3].strip("\n"))

            result.append(line[7])
            result.append(tran_results[4].strip("\n"))

            result.append(line[8])
            result.append(tran_results[5].strip("\n"))

        save_wb_active.append(result)
        print(result)

    save_wb.save(sava_xlsx)


def translate_01(sheet_name,rows):
    ...
    print(sheet_name)
    sava_xlsx = os.path.join("./", sheet_name + ".xlsx")
    save_wb = Workbook()
    save_wb_active = save_wb.active

    flag = True

    for row in rows:
        ...
        line = [col.value for col in row]
        # [53, 'ALUMINUM BROMIDE, ANHYDROUS', '7727-15-3', 1, 'Acids, Strong Non-oxidizing']
        result = []
        if flag:
            ...
            result.extend(line)
            flag = False
            print(result)
        else:
            ...
            result.extend(line[0:2])
            result.append(translate(line[1]))
            result.extend(line[2:])
            result.append(translate(line[4]))
            # print(result)
        save_wb_active.append(result)
        print(result)

    save_wb.save(sava_xlsx)


def test01():
    # _, dis = translate("鱼与熊掌不可兼得",from_lang="zh",to_lang="en")
    # print(dis)
    line =[100, 'Water and Aqueous Solutions', 0, None, 'Water is nonflammable.', 'Water reacts with many substances, including but not limited to alkali metals, hydrides, strong halogenating agents, and chlorosilanes. These reactions can be hazardous and may result in flammable or toxic gas production, or generation of excessive heat that may cause pressurization to occur. Another reactive hazard is heat of mixing. Mixing substances such as sulfuric acid or sodium hydroxide with water may generate significant heat. Additionally, water is a good solvent for polar molecules, so it can form aqueous solutions if it comes into contact with such molecules.', 'Water itself is nontoxic and is in fact essential for life. Solutes dissolved in water may be toxic, but those interactions are covered by the reactive groups that the solute belongs to.', 'This reactive group is used to account for the water component of aqueous solutions or solids containing some water (for example, nitroexplosives wetted with water to desensitize), as well as adding water itself to a mixture.', 'Water, or the water component of aqueous solutions or solids containing water.']
    tran_str = line[1] + "#\n" + line[4] + "#\n" + line[5] + "#\n" + line[6] +"#\n"+ line[7] + "#\n" + line[8]

    print(tran_str)

    dis = translate(tran_str)
    print(dis.split("#"))


def test02():
    ...
    read_xlsx("./content.xlsx")


if __name__ == '__main__':
    pass
    test02()
